import { Context } from 'koa';
import { query, queryOne, getMySQLPool } from '../database/mysql';
import { DatabaseBackup } from '../database/models';
import { utils } from '../utils/utils';
import { success, error } from '../utils/response';
import { ResponseCode } from '../config/responseCode';
import * as path from 'path';
import * as fs from 'fs';
import config from '../config';
import { consoleLog, LogType, LogLevel } from '../utils/logger';

/**
 * 创建数据库备份
 */
export async function createBackup(ctx: Context): Promise<void> {
  const { description } = ctx.request.body as { description?: string };

  // 生成格式化的时间戳：年-月-日-时-分-秒
  const now = new Date();
  const year = now.getFullYear();
  const month = String(now.getMonth() + 1).padStart(2, '0');
  const day = String(now.getDate()).padStart(2, '0');
  const hour = String(now.getHours()).padStart(2, '0');
  const minute = String(now.getMinutes()).padStart(2, '0');
  const second = String(now.getSeconds()).padStart(2, '0');
  
  const timestamp = `${year}-${month}-${day}-${hour}-${minute}-${second}`;
  const filename = `portal-server-${timestamp}.sql`;
  const backupDir = path.join(process.cwd(), 'backups');
  
  // 确保备份目录存在
  if (!fs.existsSync(backupDir)) {
    fs.mkdirSync(backupDir, { recursive: true });
  }

  // 文件的实际路径（用于写入）
  const filepath = path.join(backupDir, filename);
  // 相对路径（用于数据库记录）
  const relativePath = path.join('backups', filename);

  try {
    // 使用 mysql2 备份数据库
    const pool = getMySQLPool();
    const writeStream = fs.createWriteStream(filepath, { encoding: 'utf8' });
    
    // 写入文件头
    writeStream.write(`-- MySQL 数据库备份\n`);
    writeStream.write(`-- 生成时间: ${new Date().toISOString()}\n`);
    writeStream.write(`-- 数据库: ${config.mysql.database}\n`);
    writeStream.write(`-- 主机: ${config.mysql.host}:${config.mysql.port}\n`);
    writeStream.write(`\n`);
    writeStream.write(`SET NAMES utf8mb4;\n`);
    writeStream.write(`SET FOREIGN_KEY_CHECKS = 0;\n`);
    writeStream.write(`\n`);
    
    // 获取所有表名
    const tables = await query<{ [key: string]: string }>(
      `SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? ORDER BY TABLE_NAME`,
      [config.mysql.database]
    );
    
    if (tables.length === 0) {
      writeStream.write(`-- 数据库中没有表\n`);
      writeStream.end();
      const stats = await new Promise<fs.Stats>((resolve, reject) => {
        writeStream.on('close', () => {
          try {
            resolve(fs.statSync(filepath));
          } catch (err) {
            reject(err);
          }
        });
        writeStream.on('error', reject);
      });
      const size = stats.size;
      
      // 保存备份记录（使用相对路径）
      const id = utils.generateUUID();
      await query(
        'INSERT INTO database_backups (id, filename, filepath, size, description) VALUES (?, ?, ?, ?, ?)',
        [id, filename, relativePath, size, description || null]
      );
      
      const backup = await queryOne<DatabaseBackup>('SELECT * FROM database_backups WHERE id = ?', [id]);
      success(ctx, backup);
      return;
    }
    
    // 备份每个表
    for (const tableRow of tables) {
      // 获取表名（兼容不同的查询方式）
      let tableName: string | undefined;
      if (tableRow.TABLE_NAME) {
        tableName = tableRow.TABLE_NAME;
      } else {
        // 如果使用 SHOW TABLES，列名可能是 Tables_in_database
        const keys = Object.keys(tableRow);
        tableName = keys.length > 0 ? (tableRow as any)[keys[0]] : undefined;
      }
      
      if (!tableName) continue;
      
      writeStream.write(`\n-- ----------------------------\n`);
      writeStream.write(`-- 表结构: ${tableName}\n`);
      writeStream.write(`-- ----------------------------\n`);
      
      // 获取表结构
      const [createTableRows] = await pool.execute(`SHOW CREATE TABLE \`${tableName}\``);
      const createTableRow = (createTableRows as any[])[0];
      if (createTableRow && createTableRow['Create Table']) {
        writeStream.write(`DROP TABLE IF EXISTS \`${tableName}\`;\n`);
        writeStream.write(`${createTableRow['Create Table']};\n`);
        writeStream.write(`\n`);
      }
      
      // 获取表数据
      writeStream.write(`-- ----------------------------\n`);
      writeStream.write(`-- 表数据: ${tableName}\n`);
      writeStream.write(`-- ----------------------------\n`);
      
      const dataRows = await query<any[]>(`SELECT * FROM \`${tableName}\``);
      
      if (dataRows.length > 0) {
        // 获取列名
        const columns = Object.keys(dataRows[0]) as string[];
        const columnList = columns.map(col => `\`${col}\``).join(', ');
        
        // 分批写入数据（每批1000条）
        const batchSize = 1000;
        for (let i = 0; i < dataRows.length; i += batchSize) {
          const batch = dataRows.slice(i, i + batchSize);
          const values = batch.map(row => {
            const rowValues = columns.map(col => {
              const value = (row as any)[col];
              if (value === null || value === undefined) {
                return 'NULL';
              } else if (typeof value === 'number') {
                return String(value);
              } else if (typeof value === 'boolean') {
                return value ? '1' : '0';
              } else if (value instanceof Date) {
                return `'${value.toISOString().slice(0, 19).replace('T', ' ')}'`;
              } else {
                // 转义字符串
                const escaped = String(value)
                  .replace(/\\/g, '\\\\')
                  .replace(/'/g, "\\'")
                  .replace(/\n/g, '\\n')
                  .replace(/\r/g, '\\r')
                  .replace(/\t/g, '\\t');
                return `'${escaped}'`;
              }
            });
            return `(${rowValues.join(', ')})`;
          });
          
          writeStream.write(`INSERT INTO \`${tableName}\` (${columnList}) VALUES\n`);
          writeStream.write(values.join(',\n'));
          writeStream.write(`;\n\n`);
        }
      } else {
        writeStream.write(`-- 表 ${tableName} 没有数据\n\n`);
      }
    }
    
    // 写入文件尾
    writeStream.write(`SET FOREIGN_KEY_CHECKS = 1;\n`);
    
    // 等待写入完成
    await new Promise<void>((resolve, reject) => {
      writeStream.on('finish', resolve);
      writeStream.on('error', reject);
      writeStream.end();
    });

    // 获取文件大小
    const stats = fs.statSync(filepath);
    const size = stats.size;

    // 保存备份记录（使用相对路径）
    const id = utils.generateUUID();
    await query(
      'INSERT INTO database_backups (id, filename, filepath, size, description) VALUES (?, ?, ?, ?, ?)',
      [id, filename, relativePath, size, description || null]
    );

    const backup = await queryOne<DatabaseBackup>('SELECT * FROM database_backups WHERE id = ?', [id]);
    success(ctx, backup);
  } catch (err: any) {
    // 如果备份失败，删除可能创建的文件
    if (fs.existsSync(filepath)) {
      fs.unlinkSync(filepath);
    }
    error(ctx, ResponseCode.INTERNAL_SERVER_ERROR, `备份失败: ${err.message}`);
  }
}

/**
 * 删除备份
 */
export async function deleteBackup(ctx: Context): Promise<void> {
  const { id } = ctx.request.body as { id?: string };

  if (!id) {
    error(ctx, ResponseCode.BAD_REQUEST, '备份ID不能为空');
    return;
  }

  const backup = await queryOne<DatabaseBackup>('SELECT * FROM database_backups WHERE id = ?', [id]);
  if (!backup) {
    error(ctx, ResponseCode.NOT_FOUND, '备份不存在');
    return;
  }

  // 删除文件（将相对路径转换为绝对路径）
  const absolutePath = path.isAbsolute(backup.filepath) 
    ? backup.filepath 
    : path.join(process.cwd(), backup.filepath);
    
  if (fs.existsSync(absolutePath)) {
    try {
      fs.unlinkSync(absolutePath);
    } catch (err) {
      consoleLog(
        LogType.OTHER,
        LogLevel.ERROR,
        `删除备份文件失败: ${err instanceof Error ? err.message : String(err)}`,
        {
          error: err,
          filepath: absolutePath,
        }
      );
    }
  }

  // 删除记录
  await query('DELETE FROM database_backups WHERE id = ?', [id]);

  success(ctx, { message: '删除成功' });
}

/**
 * 查询备份列表
 */
export async function getBackupList(ctx: Context): Promise<void> {
  const { page = 1, pageSize = 10 } = ctx.request.body as {
    page?: number;
    pageSize?: number;
  };

  const sql = 'SELECT * FROM database_backups ORDER BY created_at DESC LIMIT ? OFFSET ?';
  const offset = (page - 1) * pageSize;
  const backups = await query<DatabaseBackup>(sql, [pageSize, offset]);

  const totalResult = await queryOne<{ count: number }>('SELECT COUNT(*) as count FROM database_backups');

  success(ctx, {
    list: backups,
    total: totalResult?.count || 0,
    page,
    pageSize,
  });
}

/**
 * 下载单个备份文件
 */
export async function downloadBackup(ctx: Context): Promise<void> {
  const { id } = ctx.request.body as { id?: string };

  if (!id) {
    error(ctx, ResponseCode.BAD_REQUEST, '备份ID不能为空');
    return;
  }

  const backup = await queryOne<DatabaseBackup>('SELECT * FROM database_backups WHERE id = ?', [id]);
  if (!backup) {
    error(ctx, ResponseCode.NOT_FOUND, '备份不存在');
    return;
  }

  // 将相对路径转换为绝对路径
  const absolutePath = path.isAbsolute(backup.filepath) 
    ? backup.filepath 
    : path.join(process.cwd(), backup.filepath);

  // 检查文件是否存在
  if (!fs.existsSync(absolutePath)) {
    error(ctx, ResponseCode.NOT_FOUND, '备份文件不存在');
    return;
  }

  try {
    // 设置响应头，支持文件下载
    ctx.set('Content-Type', 'application/octet-stream');
    ctx.set('Content-Disposition', `attachment; filename="${encodeURIComponent(backup.filename)}"`);
    ctx.set('Content-Length', String(backup.size));
    
    // 创建文件读取流并返回
    ctx.body = fs.createReadStream(absolutePath);
  } catch (err: any) {
    consoleLog(
      LogType.OTHER,
      LogLevel.ERROR,
      `下载备份文件失败: ${err instanceof Error ? err.message : String(err)}`,
      {
        error: err,
        filepath: absolutePath,
        backupId: id,
      }
    );
    error(ctx, ResponseCode.INTERNAL_SERVER_ERROR, `下载失败: ${err.message}`);
  }
}

